package com.apobates.jforum.grief.schema2doc.reactive.dialect;

import com.apobates.jforum.grief.schema2doc.core.SchemaTableStrategy;
import com.apobates.jforum.grief.schema2doc.core.entity.Column;
import com.apobates.jforum.grief.schema2doc.core.entity.Nullable;
import com.apobates.jforum.grief.schema2doc.core.entity.PrimayKey;
import com.apobates.jforum.grief.schema2doc.core.entity.Table;
import com.apobates.jforum.grief.schema2doc.core.schema.SchemaDialect;
import com.apobates.jforum.grief.schema2doc.reactive.schema.AbstractRxJavaQueryExecutor;
import io.reactivex.Flowable;
import io.reactivex.Single;
import org.davidmoten.rx.jdbc.Database;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.List;
import java.util.Optional;

/**
 * RxJava2的Oracle的结果集供应商
 * https://github.com/davidmoten/rxjava-jdbc
 */
public class RxJavaOracleExecutor extends AbstractRxJavaQueryExecutor {
    private final static Logger logger = LoggerFactory.getLogger(RxJavaOracleExecutor.class);

    protected RxJavaOracleExecutor(Database db, SchemaDialect dialect) {
        super(db, dialect);
    }

    @Override
    protected Flowable<Table> getTables(Database db, Optional<String> dbName, Optional<String> schema, SchemaTableStrategy tableStrategy) throws IllegalArgumentException {
        // SELECT TABLE_NAME,COMMENTS AS REMARKS FROM USER_TAB_COMMENTS WHERE TABLE_TYPE = 'TABLE'
        // DBA: sql = "SELECT TABLE_NAME,COMMENTS AS REMARKS FROM DBA_TAB_COMMENTS WHERE TABLE_TYPE = 'TABLE' AND OWNER = '"+ getSchema() + "'";
        String sql = "SELECT t.TABLE_NAME,tc.COMMENTS FROM ALL_TABLES t LEFT JOIN ALL_TAB_COMMENTS tc ON tc.TABLE_NAME = t.TABLE_NAME and tc.TABLE_TYPE = 'TABLE' WHERE t.owner = :schema ";
        // "SELECT ORIGIN_CON_ID, TABLE_NAME, COMMENTS FROM USER_TAB_COMMENTS WHERE TABLE_TYPE = 'TABLE'";
        if(tableStrategy.sql(SchemaDialect.Oracle).isPresent()){
            sql+= " AND " + tableStrategy.sql(SchemaDialect.Oracle).get().replaceAll(SchemaTableStrategy.placeholder, "t.TABLE_NAME");
        }
        logger.debug("[RxJava-Oracle-Table]execute sql statement::"+sql);
        return db.select(sql).parameter("schema", schema.get()).get(record -> {
            // String dbName, String tableName, String remarks
            return Table.noDatabase(
                    0,
                    schema.get(),
                    record.getString("TABLE_NAME"),
                    record.getString("COMMENTS"));
        }).filter(table->tableStrategy.filter().test(table.getTableName()));
    }

    @Override
    protected Single<List<Column>> getColumns(Database db, Table table) {
        // sql = "SELECT ut.TABLE_NAME, ut.COLUMN_NAME, uc.comments as REMARKS, concat(concat(concat(ut.DATA_TYPE, '('), ut.DATA_LENGTH), ')') AS COLUMN_TYPE, ut.DATA_LENGTH as COLUMN_LENGTH
        // FROM user_tab_columns ut
        // INNER JOIN user_col_comments uc ON ut.TABLE_NAME = uc.table_name AND ut.COLUMN_NAME = uc.column_name
        // WHERE ut.Table_Name = '%s'";
        final List<String> pks = getPrimayKey(db, table.getTableName());
        String sql= "SELECT ut.COLUMN_NAME, uc.COMMENTS as REMARKS, concat(concat(concat(ut.DATA_TYPE, '('), ut.DATA_LENGTH), ')') AS COLUMN_TYPE, ut.DATA_LENGTH as COLUMN_LENGTH, ut.NULLABLE, ut.DATA_DEFAULT, ut.DATA_SCALE FROM ALL_TAB_COLUMNS ut LEFT JOIN ALL_COL_COMMENTS uc ON ut.TABLE_NAME = uc.table_name AND ut.COLUMN_NAME = uc.column_name WHERE ut.Table_Name = :tablename";
        // "SELECT ut.COLUMN_NAME, uc.comments as REMARKS, concat(concat(concat(ut.DATA_TYPE, '('), ut.DATA_LENGTH), ')') AS COLUMN_TYPE, ut.DATA_LENGTH as COLUMN_LENGTH, ut.NULLABLE, ut.DATA_DEFAULT, ut.DATA_SCALE FROM user_tab_columns ut INNER JOIN user_col_comments uc ON ut.TABLE_NAME = uc.table_name AND ut.COLUMN_NAME = uc.column_name WHERE ut.Table_Name = :tablename ";
        logger.debug("[RxJava-Oracle-COLUMN]execute sql statement::"+sql);
        // Fetch results using jOOQ
        return db.select(sql).parameter("tablename", table.getTableName()).get(record -> {
            // Basic:: String columnName, String typeName, String columnSize, Nullable nullable, String remarks
            String columnName = record.getString("COLUMN_NAME");
            return Column.basic(
                            columnName,
                            record.getString("COLUMN_TYPE"),
                            record.getString("COLUMN_LENGTH"),
                            Nullable.of(record.getString("NULLABLE").equals("Y")?1:2),
                            record.getString("REMARKS"))
                    .toFull(record.getString("DATA_SCALE"),
                            pks.contains(columnName)? PrimayKey.YES:PrimayKey.NO,
                            record.getString("DATA_DEFAULT")); // String decimalDigits, PrimayKey pk, String columnDef
        }).toList();
    }

    private List<String> getPrimayKey(Database db, String tableName){
        String sql = "SELECT C.COLUMN_NAME, C.POSITION AS KEY_SEQ, C.CONSTRAINT_NAME AS PK_NAME FROM ALL_CONS_COLUMNS C, ALL_CONSTRAINTS K WHERE K.CONSTRAINT_TYPE = 'P' AND K.CONSTRAINT_NAME = C.CONSTRAINT_NAME AND K.TABLE_NAME = C.TABLE_NAME AND K.OWNER = C.OWNER AND C.TABLE_NAME = :TN";
        return db.select(sql).parameter("TN", tableName).get(record -> {
            return record.getString("COLUMN_NAME");
        }).toList().blockingGet();
    }
}
